﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using ZX.TR.Library;
using ZX.BLL.Extend;
using ZX.SYS.Library;
using ZX.Common;
using ZX.EHR.Library;
using ZX.DBModule;
using ZX.DAL;
using ZX.DAL.DBExtend;
using System.Data;

namespace ZX.TR.Bussiness
{
    public class BTrQuestionnaireSubject : BLL.Extend.BaseBussnies
    {
        /// <summary>
        /// 判断问卷名是否存在
        /// </summary>
        /// <param name="QuestionnaireName">问卷名称</param>
        /// <returns>true:已存在 false:不存在</returns>
        public bool GetQuestionnaireNameIsExists(string QuestionnaireName)
        {
            this.entity = new V_TrQuestionnaire();
            return GetEntityByCondition("QuestionnaireName='" + QuestionnaireName + "'") != null;
        }

        /// <summary>
        /// 问卷ID获取问卷信息
        /// </summary>
        /// <param name="QuestionnaireId">ID</param>
        /// <returns></returns>
        public V_TrQuestionnaire GetAllQuestionnaireInfoById(Guid QuestionnaireId)
        {
            this.entity = new V_TrQuestionnaire();
            return GetEntityBySqlComm("select * from V_TrQuestionnaire where TrQuestionnaireID='" +CSVHelper.FmSQL(QuestionnaireId) + "'") as V_TrQuestionnaire;
        }

        /// <summary>
        /// 试卷ID获取试卷信息
        /// </summary>
        /// <param name="QuestionnaireId">ID</param>
        /// <returns></returns>
        public V_TrSendQuestion GetAllTrSendQuestionInfoById(Guid TrSendQuestionID)
        {
            this.entity = new V_TrSendQuestion();
            return GetEntityBySqlComm("select * from V_TrSendQuestion where SendQuestionID='" + CSVHelper.FmSQL(TrSendQuestionID) + "'") as V_TrSendQuestion;
        }

        /// <summary>
        /// 题目ID获取题目信息
        /// </summary>
        /// <param name="QuestionnaireId">ID</param>
        /// <returns></returns>
        public V_TrSubjectInfo GetSubjectInfoById(string SubjectId)
        {
            this.entity = new V_TrSubjectInfo();
            return GetEntityBySqlComm("select * from V_TrSubjectInfo where TrSubjectInfoID='" + CSVHelper.FmSQL(SubjectId) + "'") as V_TrSubjectInfo;
        }
        /// <summary>
        /// 获取试题类型信息
        /// </summary>
        /// <param name="QuestionnaireId">ID</param>
        /// <returns></returns>
        public IList<DatPhraseDetail> GetAllTypeInfo()
        {
            this.entity = new DatPhraseDetail();
            return GetEntitysBySqlComm<DatPhraseDetail>("SELECT PhraseID, PhraseText FROM dbo.DatPhraseDetail WHERE (ClassID = '68d74359-e99c-404a-9645-52eb8a21a051') order by PhraseID desc");
        }

        /// <summary>
        /// 获取角度信息
        /// </summary>
        /// <param name="QuestionnaireId">ID</param>
        /// <returns></returns>
        public IList<DatPhraseDetail> GetAllRoleInfo()
        {
            this.entity = new DatPhraseDetail();
            return GetEntitysBySqlComm<DatPhraseDetail>("SELECT PhraseValue, PhraseText FROM dbo.DatPhraseDetail WHERE (ClassID = '641faee1-7331-4b78-b459-89910f2dac3d') order by PhraseValue");
        }   

        /// <summary>
        /// 根据部门ID获取用户名
        /// </summary>
        /// <param name="DeptId">部门ID</param>
        /// <returns></returns>
        public IList<DatUser> GetUserNameByFCId(Guid fromId,Guid customerId)
        {
            this.entity = new DatUser();
            return GetEntitysBySqlComm<DatUser>("select UserId,UName from datuser where userid in(select touserid from dbo.CrmCustomerShare where customid='" + CSVHelper.FmSQL(customerId) + "' and fromuserid='" + CSVHelper.FmSQL(fromId) + "')");
        }

        /// <summary>
        /// 根据部门ID获取用户名
        /// </summary>
        /// <param name="DeptId">部门ID</param>
        /// <returns></returns>
        public IList<DatUser> GetUserNameByDeptId(Guid DeptId)
        {
            this.entity = new DatUser();
            return GetEntitysBySqlComm<DatUser>("exec sp_GetUNameByDeptID '" + DeptId + "'");
        }        

        /// <summary>
        /// 根据问卷ID获取试题     
        /// </summary>
        /// <param name="TrQuestionnaireID"></param>
        /// <returns></returns>
        public IList<V_TrExam> GetExamListById(Guid TrQuestionnaireID)
        {
            this.entity = new V_TrExam();
            string sql = @"select * from v_trexam where 1=1                             
                            and TrQuestionnaireID='{0}' 
                            ORDER BY Sequence";
            return GetEntitysBySqlComm<V_TrExam>(string.Format(sql, CSVHelper.FmSQL(TrQuestionnaireID)));
        }

        /// <summary>
        /// 根据问卷ID和条件获取试题
        /// </summary>
        /// <param name="TrQuestionnaireID"></param>
        /// <param name="condcondition"></param>
        /// <returns></returns>
        public IList<V_TrExam> GetExamListById(Guid TrQuestionnaireID, string condcondition)
        {
            this.entity = new V_TrExam();
            string sql = @"select * from v_trexam where 1=1                             
                            and TrQuestionnaireID='{0}' 
                            and {1}
                            ORDER BY Sequence";
            return GetEntitysBySqlComm<V_TrExam>(string.Format(sql, CSVHelper.FmSQL(TrQuestionnaireID), condcondition));
        }

       /// <summary>
        /// 根据题目类型和题目角度查询出题目信息
        /// </summary>
        /// <param name="SubjectType">题目类型</param>
        /// <returns></returns>
        public IList<V_TrSubjectInfo> GetSubjectInfoToName(Guid SubjectType)
        {
            this.entity = new V_TrSubjectInfo();
            return GetEntitysBySqlComm<V_TrSubjectInfo>("select * from V_TrSubjectInfo where SubjectType='" + SubjectType + "'");
        }

        /// <summary>
        /// 根据题目类型和题目角度查询出题目信息
        /// </summary>
        /// <param name="condcondition">查询条件</param>
        /// <returns></returns>
        public IList<V_TrSubjectInfo> GetSubjectInfoToName(string condcondition)
        {
            this.entity = new V_TrSubjectInfo();
            string sql = "select * from V_TrSubjectInfo where 1=1 and {0}";
            return GetEntitysBySqlComm<V_TrSubjectInfo>(string.Format(sql, condcondition));
        }
        /// <summary>
        ///  根据SubjectId获取选项
        /// </summary>
        /// <param name="SubjectId">题目ID</param>
        /// <returns></returns>
        public IList<TrSubjectItem> GetSubjectInfoToItem(Guid SubjectId)
        {
            this.entity = new TrSubjectItem();
            return GetEntitysBySqlComm<TrSubjectItem>("select * from TrSubjectItem where TrSubjectInfoID='" + CSVHelper.FmSQL(SubjectId) + "' order by ItemCode");
        }

        /// <summary>
        /// 根据ID获取问卷题目信息
        /// </summary>
        /// <param name="QuestionnaireSubjectId">ID</param>
        /// <returns></returns>
        public V_TrQuestionnaireSubject GetQuestionnaireSubjectInfo(Guid QuestionnaireSubjectId)
        {
            this.entity = new V_TrQuestionnaireSubject();
            return GetEntityBySqlComm("select * from V_TrQuestionnaireSubject where TrQuestionnaireSubjectID='" + QuestionnaireSubjectId + "'") as V_TrQuestionnaireSubject;
        }

        /// <summary>
        /// 根据问卷ID获取发送问卷信息
        /// </summary>
        /// <param name="QuestionnaireId">问卷ID</param>
        /// <returns></returns>
        public IList<V_TrSendQuestion> GetAllSendQuestionInfo(Guid QuestionnaireId)
        {
            this.entity = new V_TrSendQuestion();
            return GetEntitysBySqlComm<V_TrSendQuestion>("select * from V_TrSendQuestion where TrQuestionnaireID='" + QuestionnaireId + "'");
        }

        /// <summary>
        /// 根据问卷ID获取问卷选择题统计信息
        /// </summary>
        /// <param name="QuestionnaireId">问卷ID</param>
        /// <param name="iType">题目类型【0-单选 1-多选】</param>
        /// <returns></returns>
        public IList<V_TrQuestionAnswer> GetAllQuestionAnswer(Guid QuestionnaireId,int iType)
        {
            IList<V_TrQuestionAnswer> list = null;
            this.entity = new V_TrQuestionAnswer();
            if (iType == 0)
            {
                list = GetEntitysBySqlComm<V_TrQuestionAnswer>("select * from V_TrQuestionAnswer where TrQuestionnaireID='" + QuestionnaireId + "' and SubjectType='0661d140-0571-4dd3-9c5d-b5bc51ec16f5'");
            }
            else
            {
                list = GetEntitysBySqlComm<V_TrQuestionAnswer>("select * from V_TrQuestionAnswer where TrQuestionnaireID='" + QuestionnaireId + "' and SubjectType='1661d140-0571-4dd3-9c5d-b5bc51ec16f5'");
            }
            return list;
        }

        /// <summary>
        /// 统计项
        /// </summary>
        /// <param name="QuestionnaireId">问卷ID</param>
        /// <param name="strType">单选项</param>
        /// <param name="iType">题目类型【0-单选 1-多选】</param>
        /// <returns></returns>
        public IList<V_TrQuestionAnswer> GetSubjectByItem(Guid QuestionnaireId, string strType, int iType)
        {
            IList<V_TrQuestionAnswer> list = null;
            this.entity = new V_TrQuestionAnswer();
            if (iType == 0)
            {
                list = GetEntitysBySqlComm<V_TrQuestionAnswer>("select count(*) as Sequence from V_TrQuestionAnswer where SubjectType='0661d140-0571-4dd3-9c5d-b5bc51ec16f5' and TrQuestionnaireID='" + QuestionnaireId + "' and AnswerContent='" + strType + "'");
            }
            else
            {
                list = GetEntitysBySqlComm<V_TrQuestionAnswer>("select AnswerContent from V_TrQuestionAnswer where SubjectType='1661d140-0571-4dd3-9c5d-b5bc51ec16f5' and TrQuestionnaireID='" + QuestionnaireId + "");
            }
            return list;
        } 

        /// <summary>
        /// 获取问卷信息集合
        /// </summary>
        /// <param name="QuestionnaireId">问卷ID</param>
        /// <returns></returns>
        public IList<TrQuestionnaireSubject> GetQuestionnaireSubjectInfoByQuestionnaireId(Guid QuestionnaireId)
        {
            this.entity = new TrQuestionnaireSubject();
            return GetEntitysBySqlComm<TrQuestionnaireSubject>("select * from TrQuestionnaireSubject where TrQuestionnaireID='" + QuestionnaireId + "'");
        }

        public IList<DatDepartment> GetAllDepartment()
        {
            this.entity = new DatDepartment();
            return GetEntitysBySqlComm<DatDepartment>("select * from DatDepartment");
        }

        /// <summary>
        /// 根据部门获取所有职员信息
        /// </summary>
        /// <param name="DeptName">部门名称</param>
        /// <returns></returns>
        public IList<V_TrSendQuestionnaireUser> GetAllEmployeeInfo(string DeptId)
        {
            this.entity = new V_TrSendQuestionnaireUser();
            IList<V_TrSendQuestionnaireUser> list = null;
            if (DeptId == "11") //查找所有部门
            {
                list = GetEntitysBySqlComm<V_TrSendQuestionnaireUser>("select * from V_TrSendQuestionnaireUser order by Name");
            }
            else
            {
                list = GetEntitysBySqlComm<V_TrSendQuestionnaireUser>("select * from V_TrSendQuestionnaireUser where DeptID='" +CSVHelper.FmSQL(DeptId) + "' order by Name");
            }
            return list;
        }

        /// <summary>
        /// 查询一个题目有多少人回答了
        /// </summary>
        /// <param name="questionnaireID"></param>
        /// <param name="subjectInfoID"></param>
        /// <returns></returns>
        public decimal GetAnswerSubjectInfoTotalPeopleNum(string questionnaireID, string subjectInfoID)
        {
            decimal totalPeopleNum = 0;
            string sql = @"select COUNT(*) as totalCount from TrQuestionnaireAnswer 
                        where TrQuestionnaireID='{0}'  group by TrSubjectInfoID  having TrSubjectInfoID='{1}' ";
            DbSql dbsql = new DbSql();
            DataTable dt = dbsql.ExecuteQuery(string.Format(sql, questionnaireID, subjectInfoID));
            if (dt.Rows.Count > 0)
            {
                decimal.TryParse(dt.Rows[0]["totalCount"].ToString(), out totalPeopleNum);
            }
            return totalPeopleNum;
        }

        /// <summary>
        /// 查询一个题目某个答案的得票数
        /// </summary>
        /// <param name="questionnaireID"></param>
        /// <param name="subjectInfoID"></param>
        /// <param name="subjectItemID"></param>
        /// <returns></returns>
        public decimal GetAnswerSubjectItemTotalPeopleNum(string questionnaireID, string subjectInfoID, string subjectItemID)
        {
            decimal totalPeopleNum = 0;
            string sql = @"select TrSubjectInfoID,TrSubjectItemID,COUNT(*) as totalCount
                                from TrQuestionnaireAnswer 
                                where TrQuestionnaireID='{0}' 
                                group by TrSubjectInfoID,TrSubjectItemID
                                having TrSubjectInfoID='{1}' and TrSubjectItemID='{2}' ";
            DbSql dbsql = new DbSql();
            DataTable dt = dbsql.ExecuteQuery(string.Format(sql, questionnaireID, subjectInfoID, subjectItemID));
            if (dt.Rows.Count > 0)
            {
                decimal.TryParse(dt.Rows[0]["totalCount"].ToString(), out totalPeopleNum);
            }
            return totalPeopleNum;
        }

        /// <summary>
        /// 查询一个问卷有多少人参与了
        /// </summary>
        /// <param name="questionnaireID"></param>
        /// <param name="subjectInfoID"></param>
        /// <param name="subjectItemID"></param>
        /// <returns></returns>
        public decimal GetAnswerQuestionnaireTotalPeopleNum(string questionnaireID)
        {
            decimal totalPeopleNum = 0;
            string sql = @"select distinct TrUserID  from TrQuestionnaireAnswer where 1=1
                                and TrQuestionnaireID='{0}'  ";
            DbSql dbsql = new DbSql();
            DataTable dt = dbsql.ExecuteQuery(string.Format(sql, questionnaireID));
            decimal.TryParse(dt.Rows.Count.ToString(), out totalPeopleNum);
            return totalPeopleNum;
        }

        /// <summary>
        /// 取得有效问卷份数=完整地回答完所有问题的参与调研中人数
        /// </summary>
        /// <param name="questionnaireID"></param>
        /// <returns></returns>
        public decimal GetEffectiveTotalPeopleNum(string questionnaireID)
        {
            decimal totalPeopleNum = 0;
            DbSql dbsql = new DbSql();
            //一份问卷的题目
            string sql = @"select ISNULL(dbo.Fn_GetEffectiveTotalPeopleNum(TrQuestionnaireID) ,0) as EffectiveTotalPepole 
                                from TrQuestionnaire 
                                where TrQuestionnaireID='{0}'  ";
            DataTable dt = dbsql.ExecuteQuery(string.Format(sql, questionnaireID));
            if (dt.Rows.Count > 0)
            {
                decimal.TryParse(dt.Rows[0]["EffectiveTotalPepole"].ToString(), out totalPeopleNum);
            }
            return totalPeopleNum;
        }

        /// <summary>
        /// 统计一份问卷的每个题目每个答案的得票情况
        /// </summary>
        /// <param name="questionnaireID"></param>
        /// <param name="subjectInfoID"></param>
        /// <returns></returns>
        public DataTable GetAnswerQuestionnaire(string questionnaireID, string subjectInfoID)
        {
            string sql = @"select * from V_AnswerQuestionnaire where TrQuestionnaireID='{0}' and TrSubjectInfoID='{1}' order by QuestionnaireName,SubjectName,ItemCode";
            DbSql dbsql = new DbSql();
            return dbsql.ExecuteQuery(string.Format(sql, questionnaireID, subjectInfoID));
        }

        /// <summary>
        ///  取得判断题的对、错的投票人数
        /// </summary>
        /// <param name="questionnaireID"></param>
        /// <param name="subjectInfoID"></param>
        /// <param name="answer">对，错</param>
        /// <returns></returns>
        public decimal GetAnswerJudgeTotalPeopleNum(string questionnaireID, string subjectInfoID, string answer)
        {
            decimal totalPeopleNum = 0;
            string sql = @"select COUNT(*) from TrQuestionnaireAnswer
                        where TrQuestionnaireID='{0}' and TrSubjectInfoID ='{1}'
                        and Answer='{2}' ";
            DbSql dbsql = new DbSql();
            DataTable dt = dbsql.ExecuteQuery(string.Format(sql, questionnaireID,subjectInfoID,answer));
            decimal.TryParse(dt.Rows.Count.ToString(), out totalPeopleNum);
            return totalPeopleNum;
        }

        /// <summary>
        /// 取得填空题答案
        /// </summary>
        /// <param name="questionnaireID"></param>
        /// <param name="subjectInfoID"></param>
        /// <returns></returns>
        public DataTable GetEssayQuestion(string questionnaireID, string subjectInfoID)
        {
            string sql = @"select row_number()over(order by AddTime )as RowNum,* from TrQuestionnaireAnswer 
                        where TrSubjectInfoID in (select TrSubjectInfoID from V_TrSubjectInfo where SubjectTypeName='填空题')
                        and Answer !='' and Answer is not null
                        and TrQuestionnaireID='{0}' and TrSubjectInfoID='{1}' order by AddTime";
            DbSql dbsql = new DbSql();
            return dbsql.ExecuteQuery(string.Format(sql, questionnaireID, subjectInfoID));
        }
    }
}
